const { pool } = require("../db");
const path = require("path");
const fs = require("fs").promises;
async function cleanupFiles() {
  try {
    // 查询数据库中的媒体数据
    const connection = await pool.getConnection();
    const [rows] = await connection.query(
      "SELECT path, food_id, travel_id FROM media"
    );
    connection.release();

    // 构建包含所有有效文件路径的集合
    const validFoodPaths = new Set();
    const validTravelPaths = new Set();
    for (const row of rows) {
      if (row.food_id) {
        validFoodPaths.add(path.join("public/uploads/food", row.path));
      }
      if (row.travel_id) {
        validTravelPaths.add(path.join("public/uploads/travel", row.path));
      }
    }
    // 读取本地 food 文件夹的文件列表
    const foodDir = "public/uploads/food";
    const foodFiles = await fs.readdir(foodDir);
    for (const file of foodFiles) {
      const filePath = path.join(foodDir, file);
      if (!validFoodPaths.has(filePath)) {
        await fs.unlink(filePath);
        console.log(`Deleted file from food: ${file}`);
      }
    }
    // 读取本地 travel 文件夹的文件列表
    const travelDir = "public/uploads/travel";
    const travelFiles = await fs.readdir(travelDir);
    for (const file of travelFiles) {
      const filePath = path.join(travelDir, file);
      if (!validTravelPaths.has(filePath)) {
        await fs.unlink(filePath);
        console.log(`Deleted file from travel: ${file}`);
      }
    }
  } catch (error) {
    console.error(error);
  }
}
async function cleanAvatar() {
  const connection = await pool.getConnection();
  const [rows] = await connection.query("SELECT avatar FROM user");
  connection.release();

  const validAvatarPaths = new Set();
  for (const row of rows) {
    if (row.avatar) {
      validAvatarPaths.add(
        path.join("public/uploads/avatar", row.avatar.split("/")[6])
      );
    }
  }
  const avatarDir = "public/uploads/avatar";
  const avatarFiles = await fs.readdir(avatarDir);
  for (const file of avatarFiles) {
    const filePath = path.join(avatarDir, file);
    if (!validAvatarPaths.has(filePath)) {
      await fs.unlink(filePath);
      console.log(`Deleted file from avatar: ${file}`);
    }
  }
}
async function cleanFeedback() {
  const connection = await pool.getConnection();
  const [rows] = await connection.query("SELECT images FROM feedback");
  connection.release();
  const validFeedbackPaths = new Set();
  for (const row of rows) {
    for (const img of row.images) {
      validFeedbackPaths.add(
        path.join("public/uploads/feedback", img.split("/")[6])
      );
    }
  }

  const feedbackDir = "public/uploads/feedback";
  const feedbackFiles = await fs.readdir(feedbackDir);
  for (const file of feedbackFiles) {
    const filePath = path.join(feedbackDir, file);
    if (!validFeedbackPaths.has(filePath)) {
      await fs.unlink(filePath);
      console.log(`Deleted file from feedback: ${file}`);
    }
  }
}
async function cleanComment() {
  const connection = await pool.getConnection();
  const [rows] = await connection.query(
    "SELECT media_urls FROM comment where media_urls is not null"
  );
  connection.release();
  const validCommentPaths = new Set();
  for (const row of rows) {
    for (const img of row.media_urls) {
      validCommentPaths.add(
        path.join("public/uploads/comment", img.split("/")[6])
      );
    }
  }
  const commentDir = "public/uploads/comment";
  const commentFiles = await fs.readdir(commentDir);
  for (const file of commentFiles) {
    const filePath = path.join(commentDir, file);
    if (!validCommentPaths.has(filePath)) {
      await fs.unlink(filePath);
      console.log(`Deleted file from comment: ${file}`);
    }
  }
}
module.exports = { cleanupFiles, cleanAvatar, cleanFeedback, cleanComment };
